﻿using System;
using System.Data;
using System.Data.SqlClient;
using Entities;

namespace DAL
{
    public class DAL_MenuURL
    {
        public const string usp_SelectMenuURLsAll       = "usp_SelectMenuURLsAll";
        public const string usp_SelectMenuURL           = "usp_SelectMenuURL";
        public const string usp_SelectMenuURLsType      = "usp_SelectMenuURLsType";
        public const string usp_SelectMenuURLsDynamic   = "usp_SelectMenuURLsDynamic";
        public const string usp_DeleteMenuURL           = "usp_DeleteMenuURL";
        public const string usp_SelectMenuURLsMaxColumn = "usp_SelectMenuURLsMaxColumn";
        public const string usp_InsertUpdateMenuURL     = "usp_InsertUpdateMenuURL";

        public DataTable Get_MenuURL_All()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectMenuURLsAll, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_MenuURL(Int64 pID)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectMenuURL, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.Int32;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@ID";
            sqlParameter[0].Value = pID;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public int Get_MenuURL_MaxColumn()
        {
            int intReturn;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectMenuURLsMaxColumn, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            try
            {
                Utils.sqlConnection.Open();
                intReturn=Convert.ToInt32(sqlCommand.ExecuteScalar());
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                intReturn = 0; ;
            }
            return intReturn;
        }

        public DataTable Get_MenuURL_Type(string pType)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectMenuURLsType, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.String;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@TYPE";
            sqlParameter[0].Value = pType;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_MenuURL_Dynamic(string pCondition)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectMenuURLsDynamic, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.String;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@WhereCondition";
            sqlParameter[0].Value = pCondition;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public bool DeleteMenuURL(Int64 pID)
        {
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_DeleteMenuURL, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.Int64;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@ID";
            sqlParameter[0].Value = pID;
            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }

        public bool InsertUpdate_MenuURL(MenuURL pMenuURL)
        {
            SqlParameter[] sqlParameter = new SqlParameter[7];
            SqlCommand sqlCommand = new SqlCommand(usp_InsertUpdateMenuURL, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.Int64;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@ID";
            sqlParameter[0].Value = pMenuURL.ID;

            sqlParameter[1] = new SqlParameter();
            sqlParameter[1].DbType = DbType.Int32;
            sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[1].ParameterName = "@ID_PARENT";
            sqlParameter[1].Value = pMenuURL.ID_PARENT;

            sqlParameter[2] = new SqlParameter();
            sqlParameter[2].DbType = DbType.String;
            sqlParameter[2].Direction = ParameterDirection.Input;
            sqlParameter[2].ParameterName = "@NAME";
            sqlParameter[2].Value = pMenuURL.NAME;

            sqlParameter[3] = new SqlParameter();
            sqlParameter[3].DbType = DbType.String;
            sqlParameter[3].Direction = ParameterDirection.Input;
            sqlParameter[3].ParameterName = "@URL";
            sqlParameter[3].Value = pMenuURL.URL;

            sqlParameter[4] = new SqlParameter();
            sqlParameter[4].DbType = DbType.String;
            sqlParameter[4].Direction = ParameterDirection.Input;
            sqlParameter[4].ParameterName = "@DESCRIPTION";
            sqlParameter[4].Value = pMenuURL.DESCRIPTION;

            sqlParameter[5] = new SqlParameter();
            sqlParameter[5].DbType = DbType.Int64;
            sqlParameter[5].Direction = ParameterDirection.Input;
            sqlParameter[5].ParameterName = "@SEQUENCE";
            sqlParameter[5].Value = pMenuURL.SEQUENCE;

            sqlParameter[6] = new SqlParameter();
            sqlParameter[6].DbType = DbType.String;
            sqlParameter[6].Direction = ParameterDirection.Input;
            sqlParameter[6].ParameterName = "@TYPE";
            sqlParameter[6].Value = pMenuURL.TYPE;

            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }
    }
}
